package org.ling.xuan.rule;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import org.ling.xuan.config.SysConstant;
import org.ling.xuan.config.SystemProperties;
import org.ling.xuan.config.TestDbConfig;
import org.ling.xuan.context.CheckChannelContext;
import org.ling.xuan.context.CheckPipelineContext;
import org.ling.xuan.util.FileLogUtil;
import org.ling.xuan.util.LxStrUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class SqlContentCheckRule extends CheckRule<CheckChannelContext, CheckPipelineContext> {
    @Override
    public void check(CheckPipelineContext pipeContext, CheckChannelContext channelContext) {
        String filePath = pipeContext.getFilePath();
        List<String> ss = FileUtil.readLines(filePath, StandardCharsets.UTF_8);
        ss = ss.stream().filter(StrUtil::isNotBlank).filter(s -> !s.trim().startsWith("--")).map(s -> {
            int i =s.indexOf("--");
            if (i>0) {
                return s.substring(0,i);
            } else {
                return s;
            }
        }).collect(Collectors.toList());
        if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlCanNotCreateTableAndIndex)) {
            for (String s : ss) {
                if (hasTableDllOrIndexSql(s) ) {
                    if(!(filePath.toLowerCase().contains(".ora.")||filePath.toLowerCase().contains(".db2."))) {
                        pipeContext.error("建表,建索引语句必选独立到建表sql,以.ora.sql或者.db2.sql命名");
                    }
                    String regex  = ".*create\\s+.*\\s+index\\s+\\((.*)\\).*";
                    Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
                    Matcher matcher = pattern.matcher(s);
                    if(matcher.find()){
                        String fields=matcher.group(1);
                        if(StrUtil.isNotBlank(fields)) {
                           if( StrUtil.split(fields, ',').size()>5){
                               pipeContext.error("索引字段数不能超过5个");
                           }
                        }
                    }
                }
            }
        }
        if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertFieldNoWithQua) || SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertValueNot0Length) || SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertValueMustTrim)) {
            for (String s : ss) {
                String a = s.replace("\r\n", "\n");
                a = a.replace("\n", " ");
                String pattern = "insert\\s*.*?\\s*\\((.*?)\\)\\s*values\\s*\\((.*?)\\)";
                // 使用Pattern.CASE_INSENSITIVE标志来忽略大小写
                Pattern regex = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
                Matcher matcher = regex.matcher(a);
                if (matcher.find()) {
                    String fieldsStr = matcher.group(1);
                    String values = matcher.group(2);
                    String[] arr = values.split(",");
                    if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertFieldNoWithQua)) {
                        String[] fields = fieldsStr.split(",");
                        for (String filed : fields) {
                            if (StrUtil.isNotBlank(filed)) {
                                filed = filed.trim();
                                if ((filed.startsWith("\"") || filed.startsWith("'")) || filed.endsWith("\"") || filed.endsWith("'")) {
                                    pipeContext.error("写入语句的字段前后不能有单双引号:" + s);
                                }
                            } else {
                                pipeContext.error("写入语句的字段不能为空:" + s);
                            }
                        }
                    }
                    for (String aa : arr) {
                        if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertValueNot0Length)) {
                            if ("''".equals(aa.trim())) {
                                pipeContext.error("写入语句的values不能为空串:" + s);
                            }
                        }
                        if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlInsertValueMustTrim)) {
                            String v = aa.trim();
                            if (v.startsWith("'") && v.endsWith("'")) {
                                v = v.substring(1, v.length() - 1);
                                if (StrUtil.isNotBlank(v)) {
                                    if (v.trim().length() != v.length()) {
                                        pipeContext.error("写入语句的values不能包含空格:" + s);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        if (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlExecuteInDevDb) && (!pipeContext.getLocalAdd().isSkipSql())) {
            String sqlStr = CollUtil.join(ss, " ");
            List<String> sqlList = StrUtil.split(sqlStr, ";");
            JdbcTemplate jdbcTemplate = TestDbConfig.getJdbcTemplate();
            if (jdbcTemplate != null) {
                for (String sql : sqlList) {
                    if (StrUtil.isNotBlank(sql)) {
                        String ex = SystemProperties.getOrDefaultStr(SysConstant.replaceSqlExecMap, "");
                        Map<String, String> replaceMap = LxStrUtil.convertToMap(ex, ";", ":");
                        for (String key : replaceMap.keySet()) {
                            sql = sql.replace(key, replaceMap.getOrDefault(key, ""));
                        }
                        try {
                            FileLogUtil.log("验证执行sql:{}", sql);
                            jdbcTemplate.execute(sql);
                        } catch (Exception e) {
                            FileLogUtil.log("执行sql失败:",e);
                            pipeContext.error(StrUtil.format("sql执行失败:{}" , e.getMessage()));
                        }
                    }
                }
            } else {
                pipeContext.warn("开发数据库未配置,无法检测sql是否正确");
            }
        }
    }

    private static boolean hasTableDllOrIndexSql(String s) {
        String regex = ".*(drop\\s+table|create\\s+table|create\\s.*\\s+index|drop\\s+index).*";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(s);
        return matcher.find();
    }

    @Override
    public boolean match(CheckPipelineContext pipeContext, CheckChannelContext channelContext) {
        String filePath = pipeContext.getFilePath();
        boolean f = filePath.endsWith(".sql") && !filePath.toLowerCase().contains(".db2.");
        return f && (SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlCanNotCreateTableAndIndex) || SystemProperties.isTrueDefaultTrue(SysConstant.checkSqlExecuteInDevDb));
    }

    public static void main(String[] args) {
        String regex  = ".*create\\s+.*\\s+index\\s+\\((.*)\\).*";
        String sql = "create unique index (a,b,c)";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if(matcher.find()){
            System.out.println(matcher.group(1));
        }
    }
}
